package qingxin.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import qingxin.dao.AlbumDAO;
import qingxin.dbc.DatabaseConnection;
import qingxin.vo.Album;

public class AlbumDAOImpl implements AlbumDAO {

	@Override
	public Album[] getAll(int start, int limit) throws Exception{
		ArrayList<Album> albums = new ArrayList<Album>();
		String sql = "select aid, a_pid, name, introduction from album " +
				"where aid < ? order by aid desc limit ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Album album = new Album();
				
				album.setAlbumID(rs.getInt(1));
				album.setCoverID(rs.getInt(2));
				album.setName(rs.getString(3));
				album.setIntroduction(rs.getString(4));
				
				albums.add(album);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
		return albums.toArray(new Album[0]);
	}

	@Override
	public void add(Album album) throws Exception {
		String sql = "insert into album (a_pid, name, introduction) values (?,?,?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setInt(1, album.getCoverID());
			pstmt.setString(2, album.getName());
			pstmt.setString(3, album.getIntroduction());
			
			pstmt.executeUpdate();
			
			ResultSet rs = pstmt.getGeneratedKeys();
			if(rs.next()){
				album.setAlbumID(rs.getInt(1));
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void delete(int albumIDS) throws Exception {
		String sql = "delete from album where aid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, albumIDS);
			
			pstmt.execute();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public Album getByID(int albumID) throws Exception {
		Album album = new Album();
		String sql = "select aid, a_pid, name, introduction from album " +
				"where aid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, albumID);
			
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				album.setAlbumID(rs.getInt(1));
				album.setCoverID(rs.getInt(2));
				album.setName(rs.getString(3));
				album.setIntroduction(rs.getString(4));
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return album;
	}
}
